package com.CMUBiS.DB;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.*;

import com.CMUBiS.util.Account;
import com.CMUBiS.util.Address;
import com.CMUBiS.util.Bid;
import com.CMUBiS.util.Category;
import com.CMUBiS.util.Image;
import com.CMUBiS.util.Item;
import com.CMUBiS.util.User;
import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException;
import com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException;

/**
 * 
 * @author Andy (andrewtu@cmu.edu, turner.andy@gmail.com)
 * @version 0.1
 * @since 0.1
 *
 */
public class MySQLDBQuery extends DBQuery{
	protected String url = null;
	protected String driver = null;
	protected String username = null; 
	protected String password = null;
	protected boolean useConnectionPool = true;
	protected int maxSQLConnections = 200;
	protected LinkedList<Connection> connections = new LinkedList<Connection>();
	
	//TODO: update this entire file with trying connections again and throwing errors
	
	public MySQLDBQuery(){
		super();
		
		this.init();
	}
	
	// This should be read from a file
	protected void init(){
		this.url = "jdbc:mysql://10.9.78.1/cmubis";
		this.driver = "com.mysql.jdbc.Driver";
		this.username = "root";
		this.password = "testtest";
	}
	
	protected Connection initConnection(){
		try {
			Class.forName(driver).newInstance();
			Connection conn = DriverManager.getConnection(url,username,password);
			return conn;
		}
		catch(Exception e){
			System.err.println("MySQLQuery (initConnection): Could not open a new database connection to " + this.url);
			e.printStackTrace();
			return null;
		}
	}
	
	//todo: Make sure connections are actually working
	protected synchronized Connection getConnection(){
		if(!this.useConnectionPool){
			return initConnection();
		}
		else{
			if(!connections.isEmpty()){
				return connections.pop();
			}
			else
				return initConnection();
		}
	}
	
	/**
	 * As some connections may timeout if left for a long period, we'll go through them all and make sure they all work
	 */
	protected void ckeckConnections(){
		for(int i=0; i<connections.size(); i++){
			Connection conn = connections.pop();
			try {
				conn.isValid(100);
				
				// The connection worked, so readd it
				connections.offerLast(conn);
			} catch (SQLException e) {
				// The connection is closed or bad, so we don't want it
			}
		}
	}
	
	protected void forceCloseConnection(Connection conn){
		if(conn != null)
		try{
			conn.close();
		}
		catch(Exception e){
			System.err.println("MySQLQuery (closeConnection): Could not close database connection");
			e.printStackTrace();
		}
	}
	
	protected void closeConnection(Connection conn){
		if(conn != null){
			if(!this.useConnectionPool){
				// close the connection
				try{
					conn.close();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (closeConnection): Could not close database connection");
					e.printStackTrace();
				}	
			}
			else{
				// re-pool the connection
				if(connections.size()<maxSQLConnections)
					connections.offerLast(conn);
				else{
					try{
						conn.close();
					}
					catch(Exception e){
						System.err.println("MySQLQuery (closeConnection): Could not close database connection");
						e.printStackTrace();
					}
				}
			}
		}
	}
	
	public int checkUsernamePassword(String username, String password){
		Connection conn = this.getConnection();
		int userID = -1;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the username and password are correct
				//PreparedStatement statement = conn.prepareStatement("SELECT `id` FROM `users` FORCE INDEX (`usernamePasswordIndex`) WHERE `users`.`username` = ? AND `users`.`password` = ?");
				PreparedStatement statement = conn.prepareStatement("SELECT `id` FROM `users` FORCE INDEX (`usernamePasswordIndex`) WHERE `users`.`username` = BINARY(?) AND `users`.`password` = BINARY(?)");
				
				statement.setString(1, username);
				statement.setString(2, password);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, set correct to true
				if(rs.next()){
					userID = rs.getInt("id");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (correctUsernamePassword): Could not check username and password");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return userID;
	}
	
	public String makeNewAuthToken(int userID){
		// Get the new auth token
		String newAuthToken = authToken(userID);
		String authToken = null;
		
		if(newAuthToken != null){
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to update the auth token
					PreparedStatement statement = conn.prepareStatement("UPDATE `users` SET `users`.`authToken` = ? WHERE `users`.`id` = ?");
					statement.setString(1,newAuthToken);
					statement.setInt(2, userID);
					
					int success = statement.executeUpdate();
					
					// We we did update the row, then we want to return the new auth token
					if(success==1) authToken = newAuthToken;
					
					statement.close();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (makeNewAuthToken): Could not insert the auth token");
					e.printStackTrace();
				}
				
				this.closeConnection(conn);
			}
		}
		
		return authToken;
	}
	
	public boolean checkAuthToken(int userID, String authToken){
		Connection conn = this.getConnection();
		boolean correct = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to get the auth token
				PreparedStatement statement = conn.prepareStatement("SELECT `authToken` FROM `users` FORCE INDEX (`PRIMARY`) WHERE `users`.`id` = ?");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has an end date
				if(rs.next()){
					String resultAuth = rs.getString("authToken");
					if(resultAuth != null && resultAuth.equals(authToken)) correct = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkAuthToken): Could not read the auth toekn");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return correct;
	}
	
	public Boolean logout(int userID){
		Boolean loggedOut = Boolean.FALSE;	
		Connection conn = this.getConnection();
			
		if(conn != null){
			try{
				// Create the SQL statement to update the auth token
				PreparedStatement statement = conn.prepareStatement("UPDATE `users` SET `users`.`authToken` = NULL WHERE `users`.`id` = ?");
				statement.setInt(1, userID);
					
				int success = statement.executeUpdate();
					
				// We we did update the row, then we want to return the new auth token
				if(success==1) loggedOut = Boolean.TRUE;
					
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (logout): Could not update the auth token");
				e.printStackTrace();
			}
				
			this.closeConnection(conn);
		}
		
		return loggedOut;
	}
	
	public Date getItemEndDate(int itemID){
		Connection conn = this.getConnection();
		Date endDate = null;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's end date
				PreparedStatement statement = conn.prepareStatement("SELECT `endDate` FROM `items` FORCE INDEX (`PRIMARY`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has an end date
				if(rs.next()){
					endDate = this.sdf.parse(rs.getString("endDate"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItemEndTime): Could not get the item end time");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return endDate;
	}
	
	public float getCurrentBid(int itemID){
		Connection conn = this.getConnection();
		float currentBid = Float.MAX_VALUE;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's maxBid
				PreparedStatement statement = conn.prepareStatement("SELECT `currentBid` FROM `items` FORCE INDEX (`PRIMARY`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has a maxBid
				if(rs.next()){
					currentBid = rs.getFloat("currentBid");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getCurrentBid): Could not get the item's current bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return currentBid;
	}
	
	//TODO: maybe we should read this and current together since they'll both be checked
	public float getMaxBid(int itemID){
		Connection conn = this.getConnection();
		float maxBid = Float.MAX_VALUE;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's maxBid
				PreparedStatement statement = conn.prepareStatement("SELECT `maxBid` FROM `items` FORCE INDEX (`PRIMARY`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has a maxBid
				if(rs.next()){
					maxBid = rs.getFloat("maxBid");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getMaxBid): Could not get the item's max bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return maxBid;
	}
	
	public int getQuantity(int itemID){
		Connection conn = this.getConnection();
		int quantity = Integer.MIN_VALUE;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's quantity
				PreparedStatement statement = conn.prepareStatement("SELECT `quantity` FROM `items` FORCE INDEX (`PRIMARY`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If there is a result, then the item exists and has a quantity
				if(rs.next()){
					quantity = rs.getInt("quantity");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getQuantity): Could not get the item's quantity");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return quantity;
	}
	
	public Item getItem(int itemID, Boolean getImages){
		Connection conn = this.getConnection();
		Item result = null;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's details
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `items` FORCE INDEX (`PRIMARY`) WHERE `items`.`id` = ?");
				statement.setInt(1, itemID);
				
				ResultSet rs = statement.executeQuery();
				
				// If an item is returned then get the details
				if(rs.next()){
					// If we need to get the images, do that now
					ArrayList<Image> images = new ArrayList<Image>();
					
					if(getImages==true)
						images = this.getItemImages(rs.getInt("items.id"));
					
					result = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							rs.getString("items.thumbnail"),
							images);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItem): Could not get the item");
				e.printStackTrace();
				result = null;
			}
			
			this.closeConnection(conn);
		}
		
		return result;
	}
	
	public String getFirstName(int userID){
		Connection conn = this.getConnection();
		String name = null;
		
		if(conn != null){
			try{
				// Create the SQL statement to see get the item's details
				PreparedStatement statement = conn.prepareStatement("SELECT firstname FROM `users` FORCE INDEX (`PRIMARY`) WHERE `users`.`id` = ?");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				// If an item is returned then get the details
				if(rs.next()){
					name = rs.getString("firstname");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getFirstName): Could not get the user's firstname");
				e.printStackTrace();
				name = null;
			}
			
			this.closeConnection(conn);
		}
		
		return name;
	}
	
	public User getPublicUser(int userID){
		User user = null;
		
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT `username` FROM `users` WHERE id = ?");
				statement.setInt(1, userID);
				ResultSet rs = statement.executeQuery();
				
				//TODO: get the user rating etc
				// Read the user's details
				if(rs.next()){
					user = new User(userID,
							rs.getString("users.username"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getPublicUser): Could not get the user");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return user;
	}
	
	public ArrayList<Image> getItemImages(int itemID){
		Connection conn = this.getConnection();
		ArrayList<Image> images = new ArrayList<Image>();

		if(conn != null){
			try{
				// Create the SQL statement
				PreparedStatement statement = conn.prepareStatement("SELECT `imageID`,`position` FROM `item_image` FORCE INDEX (imageIDIndex) WHERE `item_image`.`itemID` = ? ORDER BY `position` ASC");
				statement.setInt(1, itemID);
				
				//TODO: shouldn't I just join these? get select the selected IDs - Andy
				
				ResultSet rs = statement.executeQuery();
				
				PreparedStatement getImagesStatement = conn.prepareStatement("SELECT `url`,`description` FROM `images` WHERE `images`.`id` = ?");
				
				// If image IDs are returned, we need to read the image URLs
				while(rs.next()){
					int imageID = rs.getInt("imageID");
					
					getImagesStatement.setInt(1, imageID);
					
					ResultSet rs2 = getImagesStatement.executeQuery();
					
					// If the image really exists, then we will add the details to the images array
					if(rs2.next()){
						Image newImg = new Image(rs.getInt("position"), rs2.getString("url"), rs2.getString("description"));
						images.add(newImg);
					}
					
					rs2.close();
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getItemImages): Could not get the images");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return images;
	}
	
	public boolean  insertThumbnail(int itemID, String URL){
		return insertImage(itemID, 0, URL, "");
	}
	
	public boolean insertImage(int itemID, int position, String URL, String description){
		boolean insertedImage = false;
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the image URL
					PreparedStatement insertImage = conn.prepareStatement("INSERT INTO `images` (URL, description) "
							+ "VALUES (?, ?)");
					insertImage.setString(1, URL);
					insertImage.setString(2, description);
					
					// Create the SQL statement to get the images ID number
					PreparedStatement getImageID = conn.prepareStatement("SELECT LAST_INSERT_ID()");
					
					// Create the SQL statement to insert the item-image link
					PreparedStatement insertItemImage = conn.prepareStatement("INSERT INTO `item_image` (itemID, imageID, position) "
											+ "VALUES (?, ?, ?)");
					insertItemImage.setInt(1, itemID);
					insertItemImage.setInt(3, position);
					
					// Create the SQL statement to update the thumbnail field
					PreparedStatement updateThumbnail = conn.prepareStatement("UPDATE `items` SET thumbnail = ? " +
						"WHERE `items`.`id` = ?");
					updateThumbnail.setInt(2, itemID);
					
					// insert the image
					insertImage.executeUpdate();
					insertImage.close();
					
					// Get the images ID
					int imageID = 0;
					ResultSet rs = getImageID.executeQuery();
					if(rs.next()){
						imageID = rs.getInt(1);
					}
					rs.close();
					getImageID.close();
					
					if(position>0){
						// Set the imageID and create the item-image link
						insertItemImage.setInt(2, imageID);
						insertItemImage.executeUpdate();
						insertItemImage.close();
					}
					else{
						// if the position is 0 then we want to update the thumbnail field
						updateThumbnail.setString(1, URL);
						updateThumbnail.executeUpdate();
						updateThumbnail.close();
					}
					
					insertedImage = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (insertImage): Could not insert image");
					e.printStackTrace();
					
					//TODO: remove bad info
					insertedImage = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return insertedImage;
	}
	
	
	public ArrayList<Bid> getCurrentBids(int userID){
		return this.getXBids(userID, "bids", "items");
	}
	
	public ArrayList<Bid> getOldBids(int userID){
		return this.getXBids(userID, "oldBids", "oldItems");
	}
	
	// People could have bidded for more than are available, if they bid, then someone bought some
	// auctionEnded
	
	
	// TODO: Buy item from win bid is different as we will be limited to the quantity of items we can buy
	// depending on the number of items that we won
	public boolean buyItemNow(int userID, int itemID, int quantity, Account account, Address address){
		boolean purchased = false;
		Connection conn = this.getConnection();
		
		if(conn != null && account != null){
			try{
				/*
				 * When buying an item we may be only be buying some of them if multiple are available
				 * we will decrease the quantity available on the item with the number we are buying
				 * If the end value is zero, we have bought them all and we will move the item and bids to 'old'
				 *
				 * 
				 * 
				 * 1. Lock the items table
				 * 2. Check the items quantity is still more than or equal to the number we want to buy
				 * 		it might not be if two people are racing to buy
				 * 3. Insert the payment first, we don't want anyone getting items for free 
				 * 4. Insert the purchase
				 * 6. update the items quantity with the new quantity
				 * 7. unlock the purchase table
				 * 8. if the item's quantity is zero, then the item's auction has now finished regardless of the intended finish time
				 * 		- move the item to the old items table
				 * 		- move the bids to the old bids table
				 */
				// Lock the table so that no one else can buy the item
				PreparedStatement lockTableStatement = conn.prepareStatement("LOCK TABLES payments WRITE, purchased WRITE, items WRITE");
				
				// Get item quantity
				PreparedStatement getQuantityStatement = conn.prepareStatement("SELECT `quantity`, `buyNowPrice` FROM `items` WHERE `items`.`id` = ?");
				getQuantityStatement.setInt(1, itemID);
				
				// insert the payment
				PreparedStatement paymentItemStatement = conn.prepareStatement("INSERT INTO `payments` (userID, itemID, quantity, price, paidDate, street, town, zip, state, nameOnCard, creditCardNo, cvv, expirationDate) "
						+ "VALUES (?, ?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?)");
				paymentItemStatement.setInt(1, userID);
				paymentItemStatement.setInt(2, itemID);
				paymentItemStatement.setInt(3, quantity);
				paymentItemStatement.setString(5, address.getStreet());
				paymentItemStatement.setString(6, address.getTown());
				paymentItemStatement.setString(7, address.getZip());
				paymentItemStatement.setString(8, getStateName(address.getState()));
				paymentItemStatement.setString(9, account.getNameOnCard());
				paymentItemStatement.setString(10, account.getCreditCardNo());
				paymentItemStatement.setString(11, account.getCVV());
				paymentItemStatement.setDate(12, new java.sql.Date(account.getExpirationDate().getTime()));
				
				// Insert the purchase
				PreparedStatement purchaseItemStatement = conn.prepareStatement("INSERT INTO `purchased` (userID, itemID, quantity, price, purchaseDate, accountID, paid, paidDate) "
						+ "VALUES (?, ?, ?, ?, NOW(), ?, 1, NOW())");
				purchaseItemStatement.setInt(1, userID);
				purchaseItemStatement.setInt(2, itemID);
				purchaseItemStatement.setInt(3, quantity);
				if(account.getAccountID()>0)
					purchaseItemStatement.setInt(5, account.getAccountID());
				else
					purchaseItemStatement.setNull(5, java.sql.Types.INTEGER);
				
				// Set quantity (to old quantity - purchased amount)
				PreparedStatement setQuantityStatement = conn.prepareStatement("UPDATE `items` SET `quantity` = ? WHERE `items`.`id` = ?");
				setQuantityStatement.setInt(2, itemID);
				
				// Unlock the purchase table
				PreparedStatement unlockTableStatement = conn.prepareStatement("UNLOCK TABLES");
				
				// Copy the item to the 'old items' table
				PreparedStatement copyItemStatement = conn.prepareStatement("INSERT INTO `oldItems` SELECT * FROM `items` WHERE `items`.`id` = ?");
				copyItemStatement.setInt(1, itemID);
				
				// Delete item statement
				PreparedStatement deleteItemStatement = conn.prepareStatement("DELETE FROM `items` WHERE `items`.`id` = ?");
				deleteItemStatement.setInt(1, itemID);
				
				// copy bids statement
				PreparedStatement copyBidsStatement = conn.prepareStatement("INSERT INTO `oldBids` SELECT * FROM `bids` WHERE `bids`.`itemID` = ?");
				copyBidsStatement.setInt(1, itemID);
				
				// Delete bids statement
				PreparedStatement deleteBidsStatement = conn.prepareStatement("DELETE FROM `bids` WHERE `bids`.`itemID` = ?");
				deleteBidsStatement.setInt(1, itemID);
				
				/*
				 * Do the operations for real
				 */
				// Lock table
				lockTableStatement.executeUpdate();
				
				// Check there is sufficient quantity
				ResultSet rs = getQuantityStatement.executeQuery();
				
				if(rs.next()){
					int dbQuantity = rs.getInt("quantity");
					float dbBuyNowPrice = rs.getInt("buyNowPrice");
					purchaseItemStatement.setFloat(4, dbBuyNowPrice*quantity);
					paymentItemStatement.setFloat(4, dbBuyNowPrice*quantity);
					
					// There must be sufficient items and the buyNowPrice must be set
					if((dbQuantity - quantity) >= 0){
						// Purchase the item
						int insertedPayment = paymentItemStatement.executeUpdate();
						
						// If the payment was successful then they did buy the item
						if(insertedPayment>0){
							purchaseItemStatement.executeUpdate();
							
							// Update quantity
							setQuantityStatement.setInt(1, dbQuantity-quantity);
							setQuantityStatement.executeUpdate();
							
							purchased = true;
						}
						
						// Unlock to allow others to attempt to buy
						unlockTableStatement.executeUpdate();
						unlockTableStatement.close();
						
						if(purchased){
							// Now check if we need to move the item and bids
							// TODO: move the maintenance of moving the data to an off-line process
							if(dbQuantity-quantity <= 0){
								copyItemStatement.executeUpdate();
								deleteItemStatement.executeUpdate();
								
								copyBidsStatement.executeUpdate();
								deleteBidsStatement.executeUpdate();
								
								
							}
						}
					}
					else{
						// Unlock to allow others to attempt to buy
						unlockTableStatement.executeUpdate();
						unlockTableStatement.close();
					}
				}
				else{
					// This should happen only in a race condition
					unlockTableStatement.executeUpdate();
					unlockTableStatement.close();
					
					System.err.println("someone has bought the item between your request!");	
				}
			}
			catch(Exception e){
				//TODO: everywhere needs to unlock if they crash
				try{
					PreparedStatement unlockTableStatement = conn.prepareStatement("UNLOCK TABLES");
					unlockTableStatement.executeUpdate();
					unlockTableStatement.close();
				}
				catch(Exception e2){ }
				
				System.err.println("MySQLQuery (buyItemNow): Could not insert purchase");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return purchased;
	}
	
	
	public void insertBid(int userID, int itemID, int quantity, float bid, float maxBid){
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				/*
				 * Unfortunately we have to write lock the tables to ensure correct information
				 * 
				 *  1. Lock the bids table to writes
				 *  2. Insert our bid (other instances attempting to bid will wait here)
				 *  3. The item has the current max Bid info, we'll read it from the item instead of scanning the bids table
				 *  4. Compare the item's current max bid and the new max bid we are inserting
				 *  5. Update the item with the new highest bid
				 *  6. We can now unlock the bids table. We know that the item will always have the max(maxBid) as no one
				 *  	else could have bid while we were processing. If they bid less, their bid will not win when it gets
				 *  	processed after we unlock the table as we updated the item with the info we just inserted
				 */
				
				// SQL to lock the items table
				PreparedStatement lockTableStatement = conn.prepareStatement("LOCK TABLES bids WRITE, items WRITE");
				
				// SQL to enter the user's bid
				PreparedStatement insertBidStatement = conn.prepareStatement("INSERT INTO `bids` (userID, itemID, quantity, bid, maxBid, bidDate) "
						+ "VALUES (?, ?, ?, ?, ?, NOW())");
				insertBidStatement.setInt(1, userID);
				insertBidStatement.setInt(2, itemID);
				insertBidStatement.setInt(3, quantity);
				insertBidStatement.setFloat(4, bid);
				insertBidStatement.setFloat(5, maxBid);
				
				// Get the item's currentBid and maxBid values (and noOfBids)
				PreparedStatement getMaxBidStatement = conn.prepareStatement("SELECT currentBid, maxBid, noOfBids, currentWinner FROM `items` FORCE INDEX (`PRIMARY`)" +
						"WHERE `items`.`id` = ?");
				getMaxBidStatement.setInt(1, itemID);
				
				// SQL to update the item with the new bids
				PreparedStatement updateItemStatement = conn.prepareStatement("UPDATE `items` SET `items`.`currentBid` = ?, `items`.`maxBid` = ?, `items`.`noOfBids` = ?, `items`.`currentWinner` = ? WHERE `items`.`id` = ?");
				updateItemStatement.setInt(5,itemID);
				
				// Unlock the tables
				PreparedStatement unlockTableStatement = conn.prepareStatement("UNLOCK TABLES");
				
				/*
				 * Execute the update procedure
				 */
				// Lock table
				lockTableStatement.executeUpdate();
				
				// Insert the bid
				insertBidStatement.executeUpdate();
				insertBidStatement.close();
				
				// Get maxBid
				ResultSet rs = getMaxBidStatement.executeQuery();
				
				if(rs.next()){
					float dbCurrentBid = rs.getFloat("currentBid");
					float dbMaxBid = rs.getFloat("maxBid");
					int dbNoOfBids = rs.getInt("noOfBids");
					int currentWinner = rs.getInt("currentWinner");
					
					if(dbMaxBid==maxBid){
						// New max bid is the same as old max bid, old user is still winning as they bidded first
						updateItemStatement.setFloat(1,dbMaxBid);
						updateItemStatement.setFloat(2,dbMaxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,currentWinner);
						
					}
					else if(dbMaxBid>maxBid){
						// The old max bid is greater, old user still winning by 0.01c
						// max bid is the same, current bid is the new bid's maxPrice+1c
						updateItemStatement.setFloat(1,maxBid + 0.01f);
						updateItemStatement.setFloat(2,dbMaxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,currentWinner);
					}
					else{
						// Else the new bid is more, the new user is winning
						// The new user's bid will be the old user's max(maxBid+1c, new bid)
						updateItemStatement.setFloat(1,(bid>dbMaxBid) ? bid : new Float(dbMaxBid + 0.01f));
						updateItemStatement.setFloat(2,maxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,userID);
					}
					
					// Update the new max bid
					updateItemStatement.executeUpdate();
					updateItemStatement.close();
				}
				
				// Unlock the bids table
				unlockTableStatement.executeUpdate();
				unlockTableStatement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (insertBid): Could not get insert bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
	}
	
	/*
	public void insertBid(int userID, int itemID, int quantity, float bid, float maxBid){
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				/*
				 * 1. For consistent budding we need to make sure no one enters a lower-maxBid when we are writing
				 * 		- otherwise they may beat the current max bid and write that they are winning
				 * 		- but they didn't beat the bid that we are halfway through writing
				 * 2. So we must stop people updating the item or entering a bid while we are updating the item
				 * 3. Whoever is winning the bidding now must have the current largest 'maxBid'
				 * 		- The current bed bid is the lowest maxBid+0.01, or just our bid if we bid more than the previous maxBid
				 * 4. The items new price will be the smallest of the 'maxBid' prices plus one cent, or the bid value
				 * 		- unless both maxBids are the same, then the bid will be a draw and the price will be maxBid
				 * 		- (we know that the bid is higher than the currentBid or this method should not have been called)
				 * 5. We'll also update the number of bids value no matter what happens
				 * 6. Lastly, just in case there was some out of order update, we'll make sure the new bids are greater than the old ones
				 
				
				// SQL to lock the items table
				PreparedStatement lockTableStatement = conn.prepareStatement("LOCK TABLES items WRITE");
				
				// Get the item's currentBid and maxBid values (and noOfBids)
				PreparedStatement getMaxBidStatement = conn.prepareStatement("SELECT currentBid, maxBid, noOfBids, currentWinner FROM `items` FORCE INDEX (`PRIMARY`)" +
						"WHERE `items`.`id` = ?");
				getMaxBidStatement.setInt(1, itemID);
				
				// SQL to update the item with the new bids
				PreparedStatement updateItemStatement = conn.prepareStatement("UPDATE `items` SET `items`.`currentBid` = ?, `items`.`maxBid` = ?, `items`.`noOfBids` = ?, `items`.`currentWinner` = ? WHERE `items`.`id` = ?");
				updateItemStatement.setInt(5,itemID);
				
				// Unlock the tables
				PreparedStatement unlockTableStatement = conn.prepareStatement("UNLOCK TABLES");
				
				// SQL to enter the user's bid
				PreparedStatement insertBidStatement = conn.prepareStatement("INSERT INTO `bids` (userID, itemID, quantity, bid, maxBid, bidDate) "
						+ "VALUES (?, ?, ?, ?, ?, NOW())");
				insertBidStatement.setInt(1, userID);
				insertBidStatement.setInt(2, itemID);
				insertBidStatement.setInt(3, quantity);
				insertBidStatement.setFloat(4, bid);
				insertBidStatement.setFloat(5, maxBid);
				
				//TODO: Andy: I feel like we may still get a sync issue here? maybe not if they'll all wait for the lock...
				//UPDATE: Andy: I actually think this is fine. anyone else have thoughts?
				lockTableStatement.executeUpdate();
				
				insertBidStatement.executeUpdate();
				insertBidStatement.close();
				
				ResultSet rs = getMaxBidStatement.executeQuery();	
				
				if(rs.next()){
					float dbCurrentBid = rs.getFloat("currentBid");
					float dbMaxBid = rs.getFloat("maxBid");
					int dbNoOfBids = rs.getInt("noOfBids");
					int currentWinner = rs.getInt("currentWinner");
					
					if(dbMaxBid==maxBid){
						// New max bid is the same as old max bid, old user is still winning as they bidded first
						updateItemStatement.setFloat(1,dbMaxBid);
						updateItemStatement.setFloat(2,dbMaxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,currentWinner);
						
					}
					else if(dbMaxBid>maxBid){
						// The old max bid is greater, old user still winning by 0.01c
						// max bid is the same, current bid is the new bid's maxPrice+1c
						updateItemStatement.setFloat(1,maxBid + 0.01f);
						updateItemStatement.setFloat(2,dbMaxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,currentWinner);
					}
					else{
						// Else the new bid is more, the new user is winning
						// The new user's bid will be the old user's max(maxBid+1c, new bid)
						updateItemStatement.setFloat(1,(bid>dbMaxBid) ? bid : new Float(dbMaxBid + 0.01f));
						updateItemStatement.setFloat(2,maxBid);
						updateItemStatement.setInt(3,dbNoOfBids+1);
						updateItemStatement.setInt(4,userID);
					}
					
					// Update the item table
					
					
					// These should not be needed as long as there are no out of order bids
					// We could also move the setting of the variables in the if-else structure. But I did it the
					// long way for clarity
					if(dbCurrentBid > newCurrentBid) newCurrentBid = dbCurrentBid;
					if(dbMaxBid > newMaxBid) newMaxBid = dbMaxBid;
					
					updateItemStatement.setFloat(1,newCurrentBid);
					updateItemStatement.setFloat(2,newMaxBid);
					updateItemStatement.setInt(3,dbNoOfBids+1);
					updateItemStatement.setInt(4,newWinningUser);
					
					updateItemStatement.executeUpdate();
				}
				unlockTableStatement.executeUpdate();
				unlockTableStatement.close();
				
				lockTableStatement.close();
				getBidsStatement.close();
				
				// Inserting the bid does not need to be donw with the lock as the info used to compare is held in the item
				insertBidStatement.executeUpdate();
				insertBidStatement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (insertBid): Could not get insert bid");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
	}*/
	
	public ArrayList<Item> getCurrentSellingItems(int userID){
		return this.getXSellingItems(userID, "items");
	}
	
	public ArrayList<Item> getOldSellingItems(int userID){
		return this.getXSellingItems(userID, "oldItems");
	}
	
	protected ArrayList<Item> getXSellingItems(int userID, String table){
		ArrayList<Item> items = new ArrayList<Item>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `" + table + "` FORCE INDEX (`userIDIndex`)" +
						"WHERE `" + table + "`.`sellerID` = ? ORDER BY `" + table + "`.`endDate` ASC");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				while(rs.next()){
					Item currentItem = new Item(rs.getInt(table + ".id"),
							rs.getString(table + ".name"),
							rs.getString(table + ".description"),
							rs.getInt(table + ".quantity"),
							rs.getFloat(table + ".startPrice"),
							rs.getFloat(table + ".reservePrice"),
							rs.getFloat(table + ".buyNowPrice"),
							rs.getFloat(table + ".currentBid"),
							rs.getFloat(table + ".maxBid"),
							rs.getInt(table + ".noOfBids"),
							this.sdf.parse(rs.getString(table + ".startDate")),
							this.sdf.parse(rs.getString(table + ".endDate")),
							rs.getInt(table + ".sellerID"),
							rs.getInt(table + ".categoryID"),
							rs.getString(table + ".thumbnail"),
							new ArrayList<Image>());
					items.add(currentItem);
				}
				
				//if(items.isEmpty()) items = null;
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getXSellingItems): Could not get the items");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return items;
	}
	
	public ArrayList<Item> getTextItems(String text){
		ArrayList<Item> items = new ArrayList<Item>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `items` WHERE MATCH (name, description)" +
						" AGAINST ('"+ text +"' IN BOOLEAN MODE) ORDER BY `items`.`endDate` ASC");
				
				ResultSet rs = statement.executeQuery();
				
				while(rs.next()){
					Item currentItem = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							rs.getString("items.thumbnail"),
							new ArrayList<Image>());
					items.add(currentItem);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getTextItems): Could not get the items");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return items;
	}
	
	public ArrayList<Item> getCategoryItems(int categoryID, int page, int itemsPP) throws Exception{
		ArrayList<Item> items = new ArrayList<Item>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement;
				
				// Only filter the category if one is provided
				if(categoryID>0){	
					// Create the SQL statement to see if the email address already exists
					statement = conn.prepareStatement("SELECT * FROM `items` FORCE INDEX (`categoryIDIndex`)" +
							"WHERE `items`.`categoryID` = ? AND `items`.`endDate` > NOW() AND `items`.`quantity` > 0 ORDER BY `items`.`endDate` ASC LIMIT ? OFFSET ?" );
					statement.setInt(1, categoryID);
					statement.setInt(2, itemsPP);
					statement.setInt(3, page*itemsPP);
				}
				else{
					// Create the SQL statement to see if the email address already exists
					statement = conn.prepareStatement("SELECT * FROM `items` FORCE INDEX (`categoryIDIndex`)" +
							"WHERE `items`.`endDate` > NOW() AND `items`.`quantity` > 0 ORDER BY `items`.`endDate` ASC LIMIT ? OFFSET ?" );	
					statement.setInt(1, itemsPP);
					statement.setInt(2, page*itemsPP);
				}
				
				ResultSet rs = statement.executeQuery();

				while(rs.next()){
					Item currentItem = new Item(rs.getInt("items.id"),
							rs.getString("items.name"),
							rs.getString("items.description"),
							rs.getInt("items.quantity"),
							rs.getFloat("items.startPrice"),
							rs.getFloat("items.reservePrice"),
							rs.getFloat("items.buyNowPrice"),
							rs.getFloat("items.currentBid"),
							rs.getFloat("items.maxBid"),
							rs.getInt("items.noOfBids"),
							this.sdf.parse(rs.getString("items.startDate")),
							this.sdf.parse(rs.getString("items.endDate")),
							rs.getInt("items.sellerID"),
							rs.getInt("items.categoryID"),
							rs.getString("items.thumbnail"),
							new ArrayList<Image>());
					items.add(currentItem);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getCategoryItems): Could not get the items");
				e.printStackTrace();
				throw e;
			}
			
			this.closeConnection(conn);
		}
		
		return items;
	}
	
	//TODO: This is going to crash if an 'oldBid' is for a new item, or current item has bids in oldBids
	// May return some expired bids if they have not been moved to 'oldBids'
	private ArrayList<Bid> getXBids(int userID, String table, String itemTable){
		ArrayList<Bid> bids = new ArrayList<Bid>();
		Connection conn = this.getConnection();

		if(conn != null){
			try{
				//TODO: maybe read the items separate? what if the user bids for the same item multiple times?
				
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `"+table+"` FORCE INDEX (`userIDIndex`) " +
						"LEFT OUTER JOIN `"+itemTable+"` on `"+table+"`.`itemID` = `"+itemTable+"`.`id` " +
						"WHERE `"+table+"`.`userID` = ? ORDER BY `"+itemTable+"`.`endDate` ASC");
				statement.setInt(1, userID);
				
				ResultSet rs = statement.executeQuery();
				
				while(rs.next()){
					Item currentItem;
					
					// If the item.id is '0' then the item is missing. This should not happen, but it could if the DB gets screwed up
					if(rs.getInt(itemTable+".id") == 0){
						currentItem = null;
					}
					else{
						currentItem= new Item(rs.getInt(itemTable+".id"),
								rs.getString(itemTable+".name"),
								rs.getString(itemTable+".description"),
								rs.getInt(itemTable+".quantity"),
								rs.getFloat(itemTable+".startPrice"),
								rs.getFloat(itemTable+".reservePrice"),
								rs.getFloat(itemTable+".buyNowPrice"),
								rs.getFloat(itemTable+".currentBid"),
								rs.getFloat(itemTable+".maxBid"),
								rs.getInt(itemTable+".noOfBids"),
								this.sdf.parse(rs.getString(itemTable+".startDate")),
								this.sdf.parse(rs.getString(itemTable+".endDate")),
								rs.getInt(itemTable+".sellerID"),
								rs.getInt(itemTable+".categoryID"),
								rs.getString(itemTable+".thumbnail"),
								new ArrayList<Image>());
					}
					//TODO: increase Bid to have all field or don't select them
					
					Bid currentBid = new Bid(rs.getInt(table+".id"),
							rs.getInt(table+".userID"),
							rs.getInt(table+".quantity"),
							rs.getFloat(table+".bid"),
							rs.getFloat(table+".maxBid"),
							this.sdf.parse(rs.getString(table+".bidDate")),
							currentItem);
					bids.add(currentBid);
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getXBids): Could not get the bids from " + table);
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return bids;
	}
	
	//TODO: make create items methods?
	
	public ArrayList<Category> getCategories(int parent) throws Exception{
		ArrayList<Category> categories = new ArrayList<Category>();
		int attemptsRemaining = 5;
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					PreparedStatement statement = conn.prepareStatement("SELECT * FROM `categories` WHERE parent = ?");
					statement.setInt(1, parent);
					ResultSet rs = statement.executeQuery();
					
					// Put all of the states in to the results vector
					while(rs.next()){
						Category category = new Category(rs.getInt("categories.id"),
								rs.getString("categories.name"),
								rs.getInt("categories.parent"));
						
						categories.add(category);
					}
					
					rs.close();
					statement.close();
					attemptsRemaining=0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (getCategories): Could not get the categories");
					e.printStackTrace();
					throw e;
				}
				
				this.closeConnection(conn);	
			}
			
			attemptsRemaining--;
		}while(attemptsRemaining>0);
			
		return categories;
	}
	
	public ArrayList<Category> getAllCategories() throws Exception{
		ArrayList<Category> categories = new ArrayList<Category>();
		int attemptsRemaining = 5;
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					PreparedStatement statement = conn.prepareStatement("SELECT * FROM `categories` ORDER BY `categories`.`id`");
					ResultSet rs = statement.executeQuery();
					
					// Put all of the states in to the results vector
					while(rs.next()){
						Category category = new Category(rs.getInt("categories.id"),
								rs.getString("categories.name"),
								rs.getInt("categories.parent"));
						
						categories.add(category);
					}
					
					rs.close();
					statement.close();
					attemptsRemaining=0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (getAllCategories): Could not get the categories");
					e.printStackTrace();
					throw e;
				}
				
				this.closeConnection(conn);	
			}
			
			attemptsRemaining--;
		}while(attemptsRemaining>0);
			
		return categories;
	}
	
	public Category getCategory(int categoryID) throws Exception{
		Category category = null;
		int attemptsRemaining = 5;
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					PreparedStatement statement = conn.prepareStatement("SELECT * FROM `categories` WHERE id = ?");
					statement.setInt(1, categoryID);
					ResultSet rs = statement.executeQuery();
					
					// Put all of the states in to the results vector
					if(rs.next()){
						category = new Category(rs.getInt("categories.id"),
								rs.getString("categories.name"),
								rs.getInt("categories.parent"));
					}
					
					rs.close();
					statement.close();
					
					attemptsRemaining=0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (getCategory): Could not get the category");
					e.printStackTrace();
					throw e;
				}
				
				this.closeConnection(conn);
			}
			
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return category;
	}
	
	public boolean insertAddress(int userID, String street, String town, String zip, int state, Boolean isDefault){
		boolean insertedAddress = false;
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
	
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the address
					PreparedStatement statement = conn.prepareStatement("INSERT INTO `addresses` (userID, street, town, zip, state, isDefault) "
											+ "VALUES (?, ?, ?, ?, ?, ?)");
					statement.setInt(1, userID);
					statement.setString(2, street);
					statement.setString(3, town);
					statement.setString(4, zip);
					statement.setInt(5, state);
					statement.setBoolean(6, isDefault);
					
					statement.executeUpdate();
					statement.close();
					
					insertedAddress = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (insertAddress): Could not insert address");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					insertedAddress = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return insertedAddress;
	}
	
	public boolean updateAddress(int id, int userID, String street, String town, String zip, int state, Boolean isDefault){
		boolean updatedAddress = false;
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
	
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the item
					PreparedStatement statement = conn.prepareStatement("UPDATE `addresses` set street = ?, town = ?, zip = ?, state = ?, isDefault = ? WHERE id = ? AND userID = ?");

					statement.setString(1, street);
					statement.setString(2, town);
					statement.setString(3, zip);
					statement.setInt(4, state);
					statement.setBoolean(5, isDefault);
					
					// Must use id and userID for security
					statement.setInt(6, id);
					statement.setInt(7, userID);
					
					//TODO: is isDefault = true then read old default address and set it to false
					//TODO: is isDefault = false, but this is the default address, then ignore and set to true
					
					statement.executeUpdate();
					statement.close();
					
					updatedAddress = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (updateAddress): Could not update address");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					updatedAddress = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return updatedAddress;
	}
	
	public int insertItem(int userID, String name, String description, float startPrice, float reservePrice, float buyNowPrice, int quantity, Date endDate, int categoryID){
		int itemID = -1;
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
	
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the statement to get the itemsID
					PreparedStatement getItemID = conn.prepareStatement("SELECT LAST_INSERT_ID()");
					
					// Create the SQL statement to insert the item
					PreparedStatement statement = conn.prepareStatement("INSERT INTO `items` (name, description, startPrice, reservePrice, buyNowPrice, quantity, startDate, endDate, sellerID, categoryID) "
											+ "VALUES (?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?)");
					statement.setString(1, name);
					statement.setString(2, description);
					statement.setFloat(3, startPrice);
					statement.setFloat(4, reservePrice);
					statement.setFloat(5, buyNowPrice);
					statement.setInt(6, quantity);
					statement.setTimestamp(7, new Timestamp(endDate.getTime()));
					statement.setInt(8, userID);
					statement.setInt(9, categoryID);
					
					statement.executeUpdate();
					statement.close();
					
					ResultSet rs = getItemID.executeQuery();
					if(rs.next()){
						itemID = rs.getInt(1);
					}
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (insertItem): Could not insert item");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					itemID = -1;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return itemID;
	}
	
	public boolean insertUser(String username, String password, String email, String firstName, String lastName){
		boolean insertedUser = false;
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the user
					PreparedStatement statement = conn.prepareStatement("INSERT INTO `users` (firstname, lastname, username, password, email,  creationDate, rating) "
											+ "VALUES (?, ?, ?, ?, ?, NOW(), 0)");
					statement.setString(1, firstName);
					statement.setString(2, lastName);
					statement.setString(3, username);
					statement.setString(4, password);
					statement.setString(5, email);
					
					statement.executeUpdate();
					statement.close();
					
					insertedUser = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					// This could happen if two people happen to try and insert the same username at the same time
					// we could sync this method, but it is unlikely. And beside, the second users registration
					// will fail anyway no matter what we do
					System.err.println("MySQLQuery (insertUser): Could not insert user");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					insertedUser = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return insertedUser;
	}
	
	public boolean insertAccount(String name, String nameOnCard, String creditCardNo, String cvv, Date expirationDate){
		boolean insertedAccount = false;
		int attemptsRemaining = 5;
		//TODO: fail if nulls passed
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					// Create the SQL statement to insert the user
					PreparedStatement statement = conn.prepareStatement("INSERT INTO `accounts` (name, nameOnCard, creditCardNo, cvv, expirationDate) "
											+ "VALUES (?, ?, ?, ?, ?)");
					statement.setString(1, name);
					statement.setString(2, nameOnCard);
					statement.setString(3, creditCardNo);
					statement.setString(4, cvv);
					statement.setDate(5, new java.sql.Date(expirationDate.getTime()));
					
					statement.executeUpdate();
					statement.close();
					
					insertedAccount = true;
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (insertAccount): Could not insert account");
					e.printStackTrace();
					
					//TODO: remove bad info
					
					insertedAccount = false;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining > 0);
		
		return insertedAccount;
	}
	
	public Account getAccount(int accountID){
		Account account = null;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `account` WHERE accountID = ?" );
				statement.setInt(1, accountID);
				ResultSet rs = statement.executeQuery();
				
				// Read the user's account
				boolean getAll = true;
				if(rs.next()){	
					account = new Account(rs.getInt("account.id"),
							rs.getString("account.name"),
							rs.getString("account.nameOnCard"),
							rs.getString("account.creditCardNo"),
							rs.getString("account.cvv"),
							rs.getDate("account.expirationDate"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getAccount): Could not get the account");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return account;
	}
	
	
	public boolean updateUser(int userID, String password, String email, String firstName, String lastName){
		Connection conn = this.getConnection();
		boolean insertedUser = false;
		
		//TODO: fail if nulls passed
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("UPDATE `users` SET firstname = ?, lastname = ?, " +
						"password = ?, email = ? WHERE `users`.`id` = ?");
				statement.setString(1, firstName);
				statement.setString(2, lastName);
				statement.setString(3, password);
				statement.setString(4, email);
				statement.setInt(5, userID);
				
				statement.executeUpdate();
				statement.close();
				
				// TODO: insert other info such as address etc
				
				insertedUser = true;
			}
			catch(Exception e){
				// This could happen if two people happen to try and insert the same username at the same time
				// we could sync this method, but it is unlikely. And beside, the second users registration
				// will fail anyway no matter what we do
				System.err.println("MySQLQuery (insertUser): Could not insert user");
				e.printStackTrace();
				
				//TODO: remove bad info
				
				insertedUser = false;
			}
			
			this.closeConnection(conn);
		}
		
		return insertedUser;
	}
	
	public Address getDefaultAddress(int userID){
		ArrayList<Address> addresses = this.getAddresses(userID, Boolean.TRUE);
		
		// We can only return the default address if there is one
		if(addresses != null && addresses.size()>0)
			return  addresses.get(0);
		else
			return null;
	}
	
	public ArrayList<Address> getAddresses(int userID){
		return this.getAddresses(userID, Boolean.FALSE);
	}
	
	private ArrayList<Address> getAddresses(int userID, Boolean onlyDefault){
		ArrayList<Address> addresses = new ArrayList<Address>();
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				String onlyDefaultString = "";
				if(onlyDefault) onlyDefaultString = " AND isDefault = 1";
				
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `addresses` WHERE userID = ?" + onlyDefaultString);
				statement.setInt(1, userID);
				ResultSet rs = statement.executeQuery();
				
				// Read the user's addresses
				boolean getAll = true;
				while(rs.next() && getAll){
					Address address = new Address(rs.getInt("addresses.id"),
							rs.getInt("addresses.userID"),
							rs.getString("addresses.street"),
							rs.getString("addresses.town"),
							rs.getString("addresses.zip"),
							rs.getInt("addresses.state"),
							rs.getBoolean("addresses.isDefault"));
					
					addresses.add(address);
					
					if(onlyDefault) getAll = false;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getAddresses): Could not get the addresses");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return addresses;
	}
	
	public Address getAddress(int addressID){
		Address address = null;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `addresses` WHERE id = ?");
				statement.setInt(1, addressID);
				ResultSet rs = statement.executeQuery();
				
				// Read the user's addresses
				if(rs.next()){
					address = new Address(rs.getInt("addresses.id"),
							rs.getInt("addresses.userID"),
							rs.getString("addresses.street"),
							rs.getString("addresses.town"),
							rs.getString("addresses.zip"),
							rs.getInt("addresses.state"),
							rs.getBoolean("addresses.isDefault"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getAddress): Could not get the address");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return address;
	}
	
	public String getStateName(int stateID){
		String state = null;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT `longName` FROM `states` WHERE `id` = ?");
				statement.setInt(1, stateID);
				ResultSet rs = statement.executeQuery();
				
				// Get the state name
				if(rs.next()){
					state = rs.getString("states.longName");
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getStateName): Could not get the state name");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return state;
	}
	
	public User getUser(int userID){
		User user = null;
		Connection conn = this.getConnection();
		
		if(conn != null){
			try{
				PreparedStatement statement = conn.prepareStatement("SELECT * FROM `users` WHERE id = ?");
				statement.setInt(1, userID);
				ResultSet rs = statement.executeQuery();
				
				//TODO: get the user rating etc
				// Read the user's details
				if(rs.next()){
					user = new User(rs.getInt("users.id"),
							rs.getString("users.firstname"),
							rs.getString("users.lastname"),
							rs.getString("users.username"),
							rs.getString("users.password"),
							rs.getString("users.email"),
							rs.getString("users.authToken"));
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (getUser): Could not get the user");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return user;
	}
	
	public boolean emailExists(String email){
		Connection conn = this.getConnection();
		boolean exists = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the email address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `email` FROM `users` WHERE `users`.`email` = ?");
				statement.setString(1, email);
				
				ResultSet rs = statement.executeQuery();
				
				// If the e-mail address does exist in the table
				if(rs.next()){
					exists = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkEmailExists): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return exists;
	}
	
	public boolean usernameExists(String username){
		Connection conn = this.getConnection();
		boolean exists = false;
		
		if(conn != null){
			try{
				// Create the SQL statement to see if the username address already exists
				PreparedStatement statement = conn.prepareStatement("SELECT `username` FROM `users` WHERE `users`.`username` = ?");
				statement.setString(1, username);
				
				ResultSet rs = statement.executeQuery();
				
				// If the username does exist in the table
				if(rs.next()){
					exists = true;
				}
				
				rs.close();
				statement.close();
			}
			catch(Exception e){
				System.err.println("MySQLQuery (checkUsernameExists): Could not read results set");
				e.printStackTrace();
			}
			
			this.closeConnection(conn);
		}
		
		return exists;
	}
	
	public ArrayList<String[]> getStates() throws Exception{
		ArrayList<String[]> states = new ArrayList<String[]>();
		int attemptsRemaining = 5;
		
		do{
			Connection conn = this.getConnection();
			
			if(conn != null){
				try{
					PreparedStatement statement = conn.prepareStatement("SELECT * FROM `states`");
					ResultSet rs = statement.executeQuery();
					
					// Put all of the states in to the results vector
					while(rs.next()){
						String[] result = new String[3];
						result[0] = rs.getString("id");
						result[1] = rs.getString("shortName");
						result[2] = rs.getString("longName");
						
						states.add(result);
					}
					
					rs.close();
					statement.close();
					
					attemptsRemaining = 0;
				}
				catch(CommunicationsException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(MySQLNonTransientConnectionException e){
					// This occurs if the SQL connection has been left open for a long time
					this.forceCloseConnection(conn);
					this.ckeckConnections();
				}
				catch(Exception e){
					System.err.println("MySQLQuery (getStates): Could not read results set");
					e.printStackTrace();
					throw e;
				}
				
				this.closeConnection(conn);
			}
		
			attemptsRemaining--;
		}while(attemptsRemaining>0);
		
		return states;
	}
}
